/*
 * ********************************************* *
 *					SDCloud Solution 2016             *
 *			SDCloud Web project DB structure.       *
 * ********************************************* *
 */

/* 
 *	=============================================
 *				Identity tables structure   
 *	=============================================
 */

CREATE TABLE IF NOT EXISTS `user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The user identifier',
  `UserName` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The user name',
  `PasswordHash` longtext COLLATE utf8_unicode_ci COMMENT 'The hashed password of this user',
  `SecurityStamp` longtext COLLATE utf8_unicode_ci COMMENT 'The security stamp',
  `RegistrationDateUtc` datetime NOT NULL COMMENT 'The UTC date when this user was registered',
  `LockoutEndDateUtc` datetime DEFAULT NULL COMMENT 'The UTC date when this user will unlok',
  `LockoutEnabled` tinyint(1) NOT NULL,
  `AccessFailedCount` int(11) NOT NULL,
  `Email` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The user registred email',
  `EmailConfirmed` tinyint(1) NOT NULL COMMENT 'Flag indicates that this email was confirmed',
  `PhoneNumber` longtext COLLATE utf8_unicode_ci COMMENT 'The user registered phone number',
  `PhoneNumberConfirmed` tinyint(1) NOT NULL COMMENT 'Flag indicates that this phone number was confirmed',
  `TwoFactorEnabled` tinyint(1) NOT NULL COMMENT 'Flag indicates that two factor authorization was enabled',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='The table that contains all users';


CREATE TABLE IF NOT EXISTS `user_claims` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `ClaimType` longtext COLLATE utf8_unicode_ci,
  `ClaimValue` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UX_UserClaims_Id` (`Id`),
  KEY `IX_UserClaims_UserId` (`UserId`),
  CONSTRAINT `FK_UserClaims_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `user` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `user_external` (
  `AccountProviderType` char(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The external authorization provider name',
  `AccountIdentifier` char(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The account identifier from external authorization provider',
  `UserId` int(11) NOT NULL COMMENT ' Link to UserId in Users table',
  `LastActiveDateUtc` datetime NOT NULL COMMENT 'Date when this external account was used for authorization',
  `IsActive` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Indicates that this external account can be used for authorize in our system',
  PRIMARY KEY (`AccountProviderType`,`AccountIdentifier`,`UserId`),
  KEY `FK_Users_User_External_UserId` (`UserId`),
  CONSTRAINT `FK_Users_User_External_UserId` FOREIGN KEY (`UserId`) REFERENCES `user` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table that contains information about external accounts for specific user';


CREATE TABLE IF NOT EXISTS `role` (
  `Id` int(11) NOT NULL,
  `Name` varchar(256) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `user_roles` (
  `UserId` int(11) NOT NULL,
  `RoleId` int(11) NOT NULL,
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `IX_UserRoles_RoleId` (`RoleId`),
  CONSTRAINT `FK_UserRoles_Roles_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `role` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK_UserRoles_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `user` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `user_settings` (
  `UserId` int(11) NOT NULL COMMENT 'Link to UserId in Users table',
  `SettingKey` char(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The setting identifier',
  `SettingValue` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The setting value',
  PRIMARY KEY (`UserId`,`SettingKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table that contains an user settings';


/* 
 *	=============================================
 *				OAuth2 identitity tables structure
 *	=============================================
 */


CREATE TABLE IF NOT EXISTS `oauth_client` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The OAuth client identifier',
  `Name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The application name',
  `IsAutoGenerated` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Flag indicates that this application was generated automatically',
  `ApplicationKey` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The unique client identifier (GUID)',
  `SharedSecret` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The unique client secret key',
  `ApplicationType` enum('Trusted','Personalized','Public') COLLATE utf8_unicode_ci NOT NULL COMMENT 'The application type',
  `UserId` int(11) DEFAULT NULL COMMENT 'The attached person for this instance of client',
  `IsActive` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Flag indicates that this application is active for allowing authorization',
  `LastActiveDateUtc` datetime NOT NULL COMMENT 'Date when this application was logged in system',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `QX_OAuthClients_ClientId` (`ApplicationKey`),
  KEY `FK_OAuthClients_Users_UserId` (`UserId`),
  CONSTRAINT `FK_OAuthClients_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `user` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table that contains a clients settings credentials for support an OAuth2 protocol';

/* 
 *	=============================================
 *				Models tables structure
 *	=============================================
 */

CREATE TABLE IF NOT EXISTS `model` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The model identifier',
  `OriginalModelId` INT(11) NULL DEFAULT NULL COMMENT 'The model indetifier to original model from public gallery',
  `UserId` int(11) NOT NULL COMMENT 'The user identifier that creates this model instance',
  `CreatedDateUtc` datetime NOT NULL COMMENT 'The date when this model was creates (UTC)',
  `ModifyDateUtc` datetime DEFAULT NULL COMMENT 'The date when this model was modified (UTC)',
  `Name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The model display name',
  `Description` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'The model description text',
  `FileName` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of file that store a xmile structure of model',
  `IsPublic` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Indicates that this model should be shown at gallery',
  PRIMARY KEY (`Id`),
  KEY `FK_Models_Users_UserId` (`UserId`),
  INDEX `FK_Models_Models_OriginalModelId` (`OriginalModelId`),
  CONSTRAINT `FK_Models_Models_OriginalModelId` FOREIGN KEY (`OriginalModelId`) REFERENCES `model` (`Id`) ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT `FK_Models_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `user` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table that contains information about all models';

CREATE TABLE IF NOT EXISTS `model_run` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The unique model run identifier',
  `ModelId` int(11) NOT NULL COMMENT 'Link to Model identifier in Models table',
  `QueuedDateUtc` datetime NOT NULL COMMENT 'Date when this model execution was queued',
  `StartDateUtc` datetime DEFAULT NULL COMMENT 'Date when this model execution was started in specified job-service',
  `EndDateUtc` datetime DEFAULT NULL COMMENT 'Date when this model execution was completed or canceled',
  `Status` int(11) NOT NULL COMMENT 'The status of current model run. Enum(''Queued'',''Waiting'',''InProgress'',''Success'',''Warning'',''Error'',''Canceled'')',
  `Priority` int(11) NOT NULL COMMENT 'The priority of this model run. Enum(''Low'',''Normal'',''High'')',
  PRIMARY KEY (`Id`),
  KEY `FK_ModelRuns_Models_ModelId` (`ModelId`),
  CONSTRAINT `FK_ModelRuns_Models_ModelId` FOREIGN KEY (`ModelId`) REFERENCES `model` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='Table that contains a model run items that pending to assign to model execution agent';

CREATE TABLE `model_stat` (
	`ModelId` INT(11) NOT NULL COMMENT 'link to model identifier in Models table',
	`TotalSuccessRunCount` INT(11) NOT NULL COMMENT 'Total count of success runs of this model',
	`TotalFailedRunCount` INT(11) NOT NULL COMMENT 'Total count of failed runs of this model',
	`MinExecutionTimeInMilliseconds` BIGINT(20) NOT NULL COMMENT 'The minimum time in milliseconds was spent for run this model',
	`MaxExecutionTimeInMilliseconds` BIGINT(20) NOT NULL COMMENT 'The maximum time in milliseconds was spent for run this model',
	`AvgExecutionTimeInMilliseconds` BIGINT(20) NOT NULL COMMENT 'The average time in milliseconds was spent for run this model',
	`MinDataSizeInBytes` BIGINT(20) NOT NULL COMMENT 'The minimum size of data for model executions',
	`MaxDataSizeInBytes` BIGINT(20) NOT NULL COMMENT 'The maximum size of data for model executions',
	`AvgDataSizeInBytes` BIGINT(20) NOT NULL COMMENT 'The average size of data for model executions',
	PRIMARY KEY (`ModelId`),
	CONSTRAINT `FK_Models_Models_Runs_Stats_ModelId` FOREIGN KEY (`ModelId`) REFERENCES `model` (`Id`)
)
COMMENT='Table that contains information about model execution statistics'
ENGINE=InnoDB
;

CREATE TABLE `tag` (
	`Id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'The tag identifier',
	`Name` CHAR(16) NOT NULL COMMENT 'The url part name',
	`Title` VARCHAR(64) NOT NULL COMMENT 'The display name',
	PRIMARY KEY (`Id`),
	INDEX `IX_Tag_Name` (`Name`)
)
COMMENT='Table that contains the information about tags'
ENGINE=InnoDB
;

CREATE TABLE `model_tag` (
	`ModelId` INT(11) NOT NULL COMMENT 'The model identifier',
	`TagId` INT(11) NOT NULL COMMENT 'The tag identifier',
	PRIMARY KEY (`ModelId`, `TagId`),
	INDEX `FK_ModelTag_TagId` (`TagId`),
	CONSTRAINT `FK_ModelTag_ModelId` FOREIGN KEY (`ModelId`) REFERENCES `model` (`Id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_ModelTag_TagId` FOREIGN KEY (`TagId`) REFERENCES `tag` (`Id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

